﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
namespace HZ.Supervise.DAL
{
	public class DAL_Hospital
	{
		public static System.Data.DataTable GetHospital(string hospitalname, string areaid, int currentpage, int pagesize, out int totalrecord)
		{
			StringBuilder sbwhere = new StringBuilder("1=1");
			if (!string.IsNullOrEmpty(hospitalname))
			{
				sbwhere.AppendFormat(" AND HospitalName Like '%{0}%'", hospitalname);
			}

			if (!string.IsNullOrEmpty(areaid))
			{
				if (areaid.Substring(0, 1).Equals("X"))
				{
					areaid = areaid.Substring(1, areaid.Length - 1);
					areaid = areaid.Replace(" ", "").Replace(",", "','");
					sbwhere.AppendFormat(" AND AreaID like '{0}%'", areaid.Trim('0'));
				}
				else
				{
					areaid = "'" + areaid.Replace(" ", "").Replace(",", "','") + "'";
					sbwhere.AppendFormat(" AND AreaID IN ({0})", areaid);
				}

			}

			string sql = string.Format("SELECT HospitalID,AreaID,HospitalName FROM dbo.TB_Hospital WHERE {0} ORDER BY AreaID", sbwhere.ToString());
			HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);

			des.CurrentPage = currentpage;
			totalrecord = des.TotalCount;

			return des.GetSpecailPage();
		}

        /// <summary>
        /// 绑定药品
        /// by张雄  2011.5.23
        /// </summary>
        /// <param name="where">where条件</param>
        /// <param name="currentpage">当前页</param>
        /// <param name="pagesize">每页的行数</param>
        /// <param name="totalrecord">参数</param>
        /// <returns>药品的数据</returns>
        public static System.Data.DataTable GetHospital1(string where,string whereHos, int currentpage, int pagesize, out int totalrecord)
        {
            string sql = string.Format("SELECT HospitalID,AreaID,HospitalName FROM dbo.TB_Hospital WHERE 1=1 {0}{1} ORDER BY AreaID",where,whereHos);
            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);

            des.CurrentPage = currentpage;

            totalrecord = des.TotalCount;

            return des.GetSpecailPage();
        }

		public static System.Data.DataTable GetHospitalName(string HospitalID, string AreaID, int currentpage, int pagesize, out int totalrecord)
		{
			StringBuilder sbwhere = new StringBuilder("1=1");
			if (!string.IsNullOrEmpty(HospitalID))
			{
				if (HospitalID != "所有医院")
				{
					HospitalID = "'" + HospitalID.Replace(" ", "").Replace(",", "','") + "'";
					sbwhere.AppendFormat("  AND HospitalID IN ({0})", HospitalID);
				}
			}

			sbwhere.AppendFormat(" AND AreaID='{0}'", AreaID);
			string sql = string.Format("SELECT  HospitalID,HospitalName from dbo.TB_Hospital WHERE {0} ORDER BY AreaID", sbwhere.ToString());

			HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);

			des.CurrentPage = currentpage;
			totalrecord = des.TotalCount;

			return des.GetSpecailPage();
		}
		public static System.Data.DataTable GetHospitalName(string Hospitalnaneid, int currentpage, int pagesize, out int totalrecord)
		{
			StringBuilder sbwhere = new StringBuilder("1=1");
			if (!string.IsNullOrEmpty(Hospitalnaneid))
			{
				if (Hospitalnaneid != "所有医院")
				{
					sbwhere.AppendFormat("  AND (HospitalID IN ('{0}') or HospitalName like '%{0}%')", Hospitalnaneid);
				}
			}

			//没有分页，故西安查询20条
			string sql = string.Format("SELECT HospitalID,HospitalName from dbo.TB_HospitalInfo WHERE {0} ORDER BY AreaID", sbwhere.ToString());


			HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);

			des.CurrentPage = currentpage;
			totalrecord = des.TotalCount;

			return des.GetSpecailPage();
		}

		/// <summary>
		/// taoqi
		/// </summary>
		/// <param name="Hospitalnaneid"></param>
		/// <param name="HospitalType"></param>
		/// <param name="currentpage"></param>
		/// <param name="pagesize"></param>
		/// <param name="totalrecord"></param>
		/// <returns></returns>
		public static System.Data.DataTable GetHospitalName_Type(string Hospitalnaneid, string aid, int currentpage, int pagesize, out int totalrecord)
		{
			StringBuilder sbwhere = new StringBuilder("1=1");
			if (!string.IsNullOrEmpty(Hospitalnaneid))
			{
				if (Hospitalnaneid != "所有医院")
				{
					sbwhere.AppendFormat("  AND (HospitalID IN ('{0}') or HospitalName like '%{0}%')", Hospitalnaneid);
				}
			}

			if (!string.IsNullOrEmpty(aid))
			{
				sbwhere.AppendFormat(" AND AreaID like '{0}%'", aid.Trim('0'));
			}
			//没有分页，故西安查询20条
            string sql = string.Format("SELECT HospitalID,HospitalName,AreaID from dbo.TB_HospitalInfo WHERE {0}  and HospitalID <> '1' ORDER BY AreaID", sbwhere.ToString());
			HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);

			des.CurrentPage = currentpage;
			totalrecord = des.TotalCount;

			return des.GetSpecailPage();
		}

		/// <summary>
		/// 获取医院级别
		/// </summary>
		/// <returns></returns>
		public static System.Data.DataTable GetHospitalType()
		{

			//2011-0328 ml 医院属性
			string sql = string.Format(@"SELECT Levels FROM TB_HospitalInfo WHERE Levels IS NOT NULL GROUP BY Levels");
			return HZ.Data.DbHelperFactory.Create().ExecuteTable(sql);
		}


		/// <summary>
		/// 获得医院的数量
		/// </summary>
		/// <returns></returns>
		public static int GetHospitalCount()
		{
			string sql = "select COUNT(distinct HospitalID) FROM  dbo.TB_PurchaseOrder where PlanTime>='2010-1-1'and PlanTime<'2011-1-1'";
			return (int)HZ.Data.DbHelperFactory.Create().ExecuteScalar(sql);
		}
		/// <summary>
		/// 获得药品数量
		/// </summary>
		/// <returns></returns>
		public static int GetGoodsCount()
		{
			string sql = "SELECT COUNT(distinct ProcureCatalogID) FROM dbo.TB_PurchaseOrderDetail";
			return (int)HZ.Data.DbHelperFactory.Create().ExecuteScalar(sql);
		}
		/// <summary>
		/// 曹鹏
		/// 2011年5月17日13:55:59
		/// 医院排名
		/// </summary>
		/// <param name="orderOfRank"></param>
		/// <param name="orderBy"></param>
		/// <param name="wherePm"></param>
		/// <param name="wherePms"></param>
		/// <param name="sortSource"></param>
		/// <param name="currentpage"></param>
		/// <param name="pagesize"></param>
		/// <param name="totalrecord"></param>
		/// <returns></returns>
		public static DataTable GeteHospsRanking1(string orderOfRank, string orderBy, string wherePm, string wherePms, string sortSource,string whereHosName, int currentpage, int pagesize, out int totalrecord)
		{

			string sql = "";
            //修改者:蒋峰(2011-06-23)
            //备  注:现在用不上
            //if (sortSource == "2")
            //{

            //    sql = string.Format("select pm,pms,A.HospitalName,A.HospitalID,PurchaseAmountTotal,DistributeAmountTotal,2 as sortsource, WarehouseAmountTotal,WareHouseRate,DistributeRate from (( select RANK () OVER(order by   {0}  DESC ) as pm, HospitalName,HospitalID,SUM(PurchaseAmount) AS PurchaseAmountTotal,SUM(DistributeAmount) as DistributeAmountTotal,SUM(WarehouseAmount) as WarehouseAmountTotal,case when SUM(WarehouseAmount) = 0 then 0 when SUM(PurchaseAmount)=0 then 0 when  CONVERT(decimal(18,3),CONVERT(float, SUM(WarehouseAmount)) / SUM(PurchaseAmount))>1 then 1 else CONVERT(decimal(18,3),CONVERT(float, SUM(WarehouseAmount)) / SUM(PurchaseAmount)) end as WareHouseRate, case when SUM(DistributeAmount) = 0 then 0 when SUM(PurchaseAmount)=0 then 0 when CONVERT(decimal(18,3),CONVERT(float, SUM(DistributeAmount)) / SUM(PurchaseAmount))>1 then 1 else CONVERT(decimal(18,3),CONVERT(float, SUM(DistributeAmount)) / SUM(PurchaseAmount)) end as DistributeRate from CACHE_STAT where 1=1  {2}  group by HospitalID,HospitalName) A left join (select RANK () OVER(order by  {0} DESC ) as pms,HospitalName, HospitalID from CACHE_STAT where 1=1 {3} group by HospitalName,HospitalID ) B on A.HospitalID=B.HospitalID and A.HospitalName=B.HospitalName) where 1=1 {4}  order by {1}", orderOfRank, orderBy, wherePm, wherePms,whereHosName);

            //}
            //else
            //{

            sql = string.Format("select pm,pms,A.HospitalID,A.HospitalName,PurchaseAmountTotal,DistributeAmountTotal,WarehouseAmountTotal,WareHouseRate,DistributeRate,A.SortSorce as SortSource from (( select RANK () OVER(order by {0} DESC ) as pm, HospitalName,HospitalID,SUM(PurchaseAmount) AS PurchaseAmountTotal,SUM(DistributeAmount) as DistributeAmountTotal,SortSorce,SUM(WarehouseAmount) as WarehouseAmountTotal,case when SUM(WarehouseAmount) = 0 then 0 when SUM(PurchaseAmount)=0 then 0 when  CONVERT(decimal(18,3),CONVERT(float, SUM(WarehouseAmount)) / SUM(PurchaseAmount))>1 then 1 else CONVERT(decimal(18,3),CONVERT(float, SUM(WarehouseAmount)) / SUM(PurchaseAmount)) end as WareHouseRate, case when SUM(DistributeAmount) = 0 then 0 when SUM(PurchaseAmount)=0 then 0 when CONVERT(decimal(18,3),CONVERT(float, SUM(DistributeAmount)) / SUM(PurchaseAmount))>1 then 1 else CONVERT(decimal(18,3),CONVERT(float, SUM(DistributeAmount)) / SUM(PurchaseAmount)) end as DistributeRate from CACHE_STAT where 1=1 {2}  group by HospitalName,HospitalID,SortSorce) A left join (select RANK () OVER(order by {0}  DESC ) as pms,HospitalName,HospitalID,SortSorce,case when SUM(WarehouseAmount) = 0 then 0 when SUM(PurchaseAmount)=0 then 0 when  CONVERT(decimal(18,3),CONVERT(float, SUM(WarehouseAmount)) / SUM(PurchaseAmount))>1 then 1 else CONVERT(decimal(18,3),CONVERT(float, SUM(WarehouseAmount)) / SUM(PurchaseAmount)) end as WareHouse, case when SUM(DistributeAmount) = 0 then 0 when SUM(PurchaseAmount)=0 then 0 when CONVERT(decimal(18,3),CONVERT(float, SUM(DistributeAmount)) / SUM(PurchaseAmount))>1 then 1 else CONVERT(decimal(18,3),CONVERT(float, SUM(DistributeAmount)) / SUM(PurchaseAmount)) end as Distribute from CACHE_STAT where 1=1 {3}  group by HospitalName,HospitalID,SortSorce ) B on A.HospitalID=B.HospitalID and A.HospitalName=B.HospitalName and A.SortSorce=B.SortSorce)  where 1=1 {4} order by {1}", orderOfRank, orderBy, wherePm, wherePms, whereHosName);

			//}

			HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);

			des.CurrentPage = currentpage;

			totalrecord = des.TotalCount;

			return des.GetSpecailPage();


		}

		/// <summary>
		/// 返回医院月排名
		/// </summary>
		/// <param name="_strmonth1">选择月份</param>
		/// <param name="_strmonth2">上月份</param>
		/// <param name="_strorderby">排序字段</param>
		///<param name="strwhere">查询条件</param>
		/// <param name="currentpage"></param>
		/// <param name="pagesize"></param>
		/// <param name="totalrecord"></param>
		/// <returns></returns>
		public static DataTable GeteHospsRanking(string Sort, string time1, string time2, string where, string orderby, string dropmode, int currentpage, int pagesize, out int totalrecord)
		{
			//sql排序等于0 转换成 无
			//张雄 modify 2011.4.21
			//2011-0427 ml 从静态表里取数据
			string sql = "";
			if (dropmode.Equals("2"))//不分类别
			{
				if (Sort == "WareHouseRate")
				{
					sql = string.Format(@"SELECT A.apm,ISNULL(b.pm,0) as pm, ISNULL(b.pm-a.apm,a.apm) as pmc,A.DistributeAmountTotal,A.DistributeCountTotal,A.PurchaseAmountTotal,A.PURCHASECOUNTTotal,A.WarehouseAmountTotal,A.WarehouseCountTotal,Case 
when ISNULL(a.WarehouseAmountTotal,0) =0 then 0 
when ISNULL(purchaseamount4daysagototal,0)=0 then 0  else CONVERT(FLOAT, A.WarehouseAmountTotal/purchaseamount4daysagototal ) end  AS WareHouseRate,HospitalName ,99 as  SortSource FROM 
                                    ( SELECT RANK() OVER (ORDER BY (SUM(WarehouseAmount)/SUM(PurchaseAmount)) DESC) apm,SUM(PURCHASECOUNT) as PurchaseCountTotal,SUM(PurchaseAmount) AS PurchaseAmountTotal,SUM(DistributeAmount) as DistributeAmountTotal,SUM(DistributeCount) as DistributeCountTotal , SUM(WarehouseAmount) as WarehouseAmountTotal,SUM(WarehouseCount)  as WarehouseCountTotal,                         
                                     SUM(PurchaseAmount) as purchaseamount4daysagototal,   HospitalID,AreaID,DayTime,HospitalName FROM 	dbo.CACHE_STAT where 1=1  and DayTime={1}  {3} GROUP BY HospitalID,HospitalName,AreaID,DayTime ) AS A 
                                        LEFT JOIN (SELECT RANK() OVER (ORDER BY (SUM(WarehouseAmount)/SUM(PurchaseAmount)) DESC) PM,SUM(PurchaseCount) as PurchaseCountTotal,SUM(PurchaseAmount) as PurchaseAmountTotal ,SUM(DistributeAmount) as DistributeAmountTotal,SUM(DistributeCount) as DistributeCountTotal,SUM(WarehouseAmount) as WarehouseAmountTotal,SUM(WarehouseCount) as WarehouseCountTotal,HospitalID,AreaID,DayTime FROM dbo.CACHE_STAT where 1=1  and DayTime={2}  {3} GROUP BY HospitalID,AreaID,DayTime) AS B ON A.HospitalID=B.HospitalID  AND A.AreaID=B.AreaID    ORDER BY apm {4} ", Sort, time1, time2, where, orderby);
				}
				else
				{
					sql = string.Format(@"SELECT A.apm,ISNULL(b.pm,0) as pm, ISNULL(b.pm-a.apm,a.apm) as pmc,A.DistributeAmountTotal,A.DistributeCountTotal,A.PurchaseAmountTotal,A.PURCHASECOUNTTotal,A.WarehouseAmountTotal,A.WarehouseCountTotal,Case 	when ISNULL(a.WarehouseAmountTotal,0)=0 then 0 
	when ISNULL(purchaseamount4daysagototal,0) = 0 then 0  else CONVERT(FLOAT, A.WarehouseAmountTotal/purchaseamount4daysagototal ) end  AS WareHouseRate,HospitalName ,99 as  SortSource FROM 
                                    ( SELECT RANK() OVER (ORDER BY SUM({0}) DESC) apm,SUM(PURCHASECOUNT) as PurchaseCountTotal,SUM(PurchaseAmount) AS PurchaseAmountTotal,SUM(DistributeAmount) as DistributeAmountTotal,SUM(DistributeCount) as DistributeCountTotal , SUM(WarehouseAmount) as WarehouseAmountTotal,SUM(WarehouseCount)  as WarehouseCountTotal,                         
                                     SUM(PurchaseAmount) as purchaseamount4daysagototal,   HospitalID,AreaID,DayTime,HospitalName FROM 	dbo.CACHE_STAT where 1=1  and DayTime={1}  {3} GROUP BY HospitalID,HospitalName,AreaID,DayTime ) AS A 
                                        LEFT JOIN (SELECT RANK() OVER (ORDER BY SUM({0}) DESC) PM,SUM(PurchaseCount) as PurchaseCountTotal,SUM(PurchaseAmount) as PurchaseAmountTotal ,SUM(DistributeAmount) as DistributeAmountTotal,SUM(DistributeCount) as DistributeCountTotal,SUM(WarehouseAmount) as WarehouseAmountTotal,SUM(WarehouseCount) as WarehouseCountTotal,HospitalID,AreaID,DayTime FROM dbo.CACHE_STAT where 1=1  and DayTime={2}  {3} GROUP BY HospitalID,AreaID,DayTime) AS B ON A.HospitalID=B.HospitalID  AND A.AreaID=B.AreaID    ORDER BY apm {4} ", Sort, time1, time2, where, orderby);
				}

			}
			else
			{
				if (Sort == "WareHouseRate")
				{
					sql = string.Format(@"SELECT A.apm,ISNULL(b.pm,0) as pm, ISNULL(b.pm-a.apm,a.apm) as pmc,A.DistributeAmountTotal,A.DistributeCountTotal,A.PurchaseAmountTotal,A.PURCHASECOUNTTotal,A.WarehouseAmountTotal,A.WarehouseCountTotal,Case when a.WarehouseAmountTotal=0 then 0 when purchaseamount4daysagototal=0 then 0 else CONVERT(FLOAT, A.WarehouseAmountTotal/purchaseamount4daysagototal ) end  AS WareHouseRate,HospitalName ,a.SortSorce as  SortSource FROM 
                                    ( SELECT RANK() OVER (ORDER BY (SUM(WarehouseAmount)/SUM(PurchaseAmount)) DESC) apm,SUM(PURCHASECOUNT) as PurchaseCountTotal,SUM(PurchaseAmount) AS PurchaseAmountTotal,SUM(DistributeAmount) as DistributeAmountTotal,SUM(DistributeCount) as DistributeCountTotal , SUM(WarehouseAmount) as WarehouseAmountTotal,SUM(WarehouseCount)  as WarehouseCountTotal,                         
                                     SUM(PurchaseAmount) as purchaseamount4daysagototal,  SortSorce, HospitalID,AreaID,DayTime,HospitalName FROM 	dbo.CACHE_STAT where 1=1  and DayTime={1}  {3} GROUP BY HospitalID,HospitalName,AreaID,DayTime,SortSorce ) AS A 
                                        LEFT JOIN (SELECT RANK() OVER (ORDER BY (SUM(WarehouseAmount)/SUM(PurchaseAmount)) DESC) PM,SUM(PurchaseCount) as PurchaseCountTotal,SUM(PurchaseAmount) as PurchaseAmountTotal ,SUM(DistributeAmount) as DistributeAmountTotal,SUM(DistributeCount) as DistributeCountTotal,SUM(WarehouseAmount) as WarehouseAmountTotal,SUM(WarehouseCount) as WarehouseCountTotal,HospitalID,AreaID,DayTime,SortSorce FROM dbo.CACHE_STAT where 1=1  and DayTime={2}  {3} GROUP BY HospitalID,AreaID,DayTime,SortSorce) AS B ON A.HospitalID=B.HospitalID  AND A.AreaID=B.AreaID  AND A.SortSorce=B.SortSorce  ORDER BY apm {4} ", Sort, time1, time2, where, orderby);
				}
				else
				{
					sql = string.Format(@"SELECT A.apm,ISNULL(b.pm,0) as pm, ISNULL(b.pm-a.apm,a.apm) as pmc,A.DistributeAmountTotal,A.DistributeCountTotal,A.PurchaseAmountTotal,A.PURCHASECOUNTTotal,A.WarehouseAmountTotal,A.WarehouseCountTotal,Case when a.WarehouseAmountTotal=0 then 0 when purchaseamount4daysagototal=0 then 0 else CONVERT(FLOAT, A.WarehouseAmountTotal/purchaseamount4daysagototal ) end  AS WareHouseRate,HospitalName,a.SortSorce as  SortSource FROM 
                                    ( SELECT RANK() OVER (ORDER BY avg({0}) DESC) apm,SUM(PURCHASECOUNT) as PurchaseCountTotal,SUM(PurchaseAmount) AS PurchaseAmountTotal,SUM(DistributeAmount) as DistributeAmountTotal,SUM(DistributeCount) as DistributeCountTotal , SUM(WarehouseAmount) as WarehouseAmountTotal,SUM(WarehouseCount)  as WarehouseCountTotal,                         
                                     SUM(PurchaseAmount) as purchaseamount4daysagototal,   HospitalID,SortSorce,AreaID,DayTime,HospitalName FROM 	dbo.CACHE_STAT where 1=1 and DayTime={1}  {3} GROUP BY HospitalID,SortSorce,HospitalName,AreaID,DayTime ) AS A 
                                        LEFT JOIN (SELECT RANK() OVER (ORDER BY SUM({0}) DESC) PM,SUM(PurchaseCount) as PurchaseCountTotal,SUM(PurchaseAmount) as PurchaseAmountTotal ,SUM(DistributeAmount) as DistributeAmountTotal,SUM(DistributeCount) as DistributeCountTotal,SUM(WarehouseAmount) as WarehouseAmountTotal,SUM(WarehouseCount) as WarehouseCountTotal,HospitalID,SortSorce,AreaID,DayTime FROM dbo.CACHE_STAT where 1=1  and DayTime={2}  {3} GROUP BY HospitalID,SortSorce,AreaID,DayTime) AS B ON A.HospitalID=B.HospitalID  AND A.AreaID=B.AreaID AND A.SortSorce=B.SortSorce   ORDER BY apm {4} ", Sort, time1, time2, where, orderby);
				}

			}


			HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);

			des.CurrentPage = currentpage;
			totalrecord = des.TotalCount;

			return des.GetSpecailPage();
		}

		/// <summary>
		/// 返回月排名(按时间段)
		/// </summary>
		/// <param name="_strmonth1">选择月份</param>
		/// <param name="_strmonth2">上月份</param>
		/// <param name="_strorderby">排序字段</param>
		///<param name="strwhere">查询条件</param>
		/// <param name="currentpage"></param>
		/// <param name="pagesize"></param>
		/// <param name="totalrecord"></param>
		/// <returns></returns>
		public static DataTable GeteHospsRankingTime(string _strmonth1, string _strorderby, string strwhere1, string strsortsource, int currentpage, int pagesize, out int totalrecord)
		{
			string sql = "";
			//=2不分类别
			//2011.04.18 张雄 modify
			//2011-04-22更改入库率依照数量计算 ml
			if (strsortsource.Equals("2"))//不分类别
			{
				sql = " select	a.pm apm,a.HospitalName,99 as  SortSource, a.PurchaseCountTotal ,a.PurchaseAmountTotal  ,a.WareHouseCountTotal , a.WareHouseRate , a.WareHouseAmountTotal from (SELECT RANK() over(order by " + _strorderby + ") pm, B.HospitalName,PurchaseCountTotal, PurchaseAmountTotal, WareHouseCountTotal, WareHouseRate, WareHouseAmountTotal, Profit FROM (SELECT HospitalName AS HospitalName,SUM(PurchaseAmount) AS PurchaseAmountTotal,SUM(PurchaseCount) AS PurchaseCountTotal,SUM(WarehouseCount) AS WareHouseCountTotal,Case when sum(WareHouseCount)=0 then null when sum(PurchaseCount)=0 then NULL else CONVERT(FLOAT,SUM(WareHouseCount)) / SUM(PurchaseCount) end  AS WareHouseRate,SUM(WarehouseAmount) AS WareHouseAmountTotal,SUM(Sellprice * PurchaseCount - Price * PurchaseCount) AS Profit FROM View_Supervise WHERE 1=1   AND (" + _strmonth1 + ") GROUP BY  HospitalName) AS B ) a order by apm";
			}
			else
			{
				sql = " select	a.pm apm,a.HospitalName,a.SortSource, a.PurchaseCountTotal ,a.PurchaseAmountTotal  ,a.WareHouseCountTotal , a.WareHouseRate , a.WareHouseAmountTotal from (SELECT RANK() over(order by " + _strorderby + ") pm, B.SortSource,B.HospitalName,PurchaseCountTotal, PurchaseAmountTotal, WareHouseCountTotal, WareHouseRate, WareHouseAmountTotal, Profit FROM (SELECT SortSource AS SortSource,HospitalName AS HospitalName,SUM(PurchaseAmount) AS PurchaseAmountTotal,SUM(PurchaseCount) AS PurchaseCountTotal,SUM(WarehouseCount) AS WareHouseCountTotal,Case when sum(WareHouseCount)=0 then null when sum(PurchaseCount)=0 then NULL else CONVERT(FLOAT,SUM(WareHouseCount)) / SUM(PurchaseCount) end  AS WareHouseRate,SUM(WarehouseAmount) AS WareHouseAmountTotal,SUM(Sellprice * PurchaseCount - Price * PurchaseCount) AS Profit FROM View_Supervise WHERE 1=1 and SortSource=" + strsortsource + " AND (" + _strmonth1 + ") GROUP BY  SortSource,HospitalName) AS B ) a order by apm";
			}


			HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);

			des.CurrentPage = currentpage;
			totalrecord = des.TotalCount;

			return des.GetSpecailPage();
		}
	}
}
